const axios = require("axios");
const XLSX = require("xlsx");
const fs = require("fs");
const path = require("path");

const inputFilePath = "Data.xls";
const outputFilePath = "example_sentences_with_word.xlsx";  // 输出文件修改为 WordRoot_Result.xlsx

function getWordsFromExcel(filePath) {
  const workbook = XLSX.readFile(filePath);
  const sheet = workbook.Sheets[workbook.SheetNames[0]];
  const data = XLSX.utils.sheet_to_json(sheet, { header: 1 });
  return data.slice(1).map(row => row[0]).filter(Boolean);
}

function loadOrCreateWorkbook(filePath, jsonKeys = []) {
  if (fs.existsSync(filePath)) {
    return XLSX.readFile(filePath);
  } else {
    const wb = XLSX.utils.book_new();
    const headers = ["Word", ...jsonKeys]; // 动态生成列头
    const ws = XLSX.utils.aoa_to_sheet([headers]);
    XLSX.utils.book_append_sheet(wb, ws, "Sheet1");
    return wb;
  }
}

function appendToExcel(word, jsonData, filePath) {
  const jsonKeys = Object.keys(jsonData);
  const workbook = loadOrCreateWorkbook(filePath, jsonKeys);
  const sheet = workbook.Sheets[workbook.SheetNames[0]];
  const data = XLSX.utils.sheet_to_json(sheet, { header: 1 });

  // 如果现有表头少了某些字段，补上
  const headers = data[0];
  jsonKeys.forEach(key => {
    if (!headers.includes(key)) headers.push(key);
  });

  // 构建新行：先放 Word，然后按 headers 的顺序放值
  const row = headers.map(header => {
    if (header === "Word") return word;
    return jsonData[header] || "";
  });

  data.push(row);

  // 重新生成表格并写入
  const newSheet = XLSX.utils.aoa_to_sheet(data);
  workbook.Sheets[workbook.SheetNames[0]] = newSheet;
  XLSX.writeFile(workbook, filePath);
}


function getWrittenWords(filePath) {
  if (!fs.existsSync(filePath)) return new Set();
  const workbook = XLSX.readFile(filePath);
  const sheet = workbook.Sheets[workbook.SheetNames[0]];
  const data = XLSX.utils.sheet_to_json(sheet, { header: 1 });
  return new Set(data.slice(1).map(row => row[0]));
}

async function getRootWordsFromAI(word) {
  try {
    const response = await axios.post("http://localhost:4588/ai-chat", {
      messages: [{
        role: "user",
        content: `请用英文给出一个包含单词 "${word}" 的例句,例句稍微难一点，并提供中文翻译。数据返回为json格式为{"Sentence":"","Translation":""}`
      }]
    });

    let content = response.data?.choices?.[0]?.message?.content?.trim();
    console.log(`API 返回内容: ${content}`);

    content = content.replace(/```json|```/g, '').trim();

    const parsed = JSON.parse(content);
    console.log(`解析后的数据: ${JSON.stringify(parsed)}`);

    return parsed; // 返回 JSON 对象数组
  } catch (error) {
    console.error(`❌ 获取 "${word}" 相关词根失败:`, error.message);
    return null;
  }
}

async function main() {
  const words = getWordsFromExcel(inputFilePath);
  const writtenWords = getWrittenWords(outputFilePath);
  console.log(`🔍 共 ${words.length} 个单词，将每 0.5 秒请求一次，跳过已写入的单词`);

  for (let i = 0; i < words.length; i++) {
    const word = words[i];
    if (writtenWords.has(word)) {
      console.log(`⏩ 已跳过（已存在）：${word}`);
      continue;
    }

    console.log(`\n📌 [${i + 1}/${words.length}] 处理：${word}`);

    let result = null;
    let retryCount = 0;

    while (!result) {
      result = await getRootWordsFromAI(word);
      retryCount++;

      if (!result) {
        console.log(`🔁 第 ${retryCount} 次重试 "${word}"...`);
        await new Promise(resolve => setTimeout(resolve, 1000));
      }
    }

    appendToExcel(word, result, outputFilePath);
    console.log(`✅ 已写入：${word}`);

    if (i < words.length - 1) {
      await new Promise(resolve => setTimeout(resolve, 500));
    }
  }

  console.log("\n🎉 所有单词处理完成！");
}

main();
